package com.zsl.dao.impl;

import com.zsl.dao.BookDao;
import com.zsl.model.Book;
import com.zsl.model.Users;
import com.zsl.util.C3P0Util;
import com.zsl.util.DbHelper;

import java.io.Console;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BookDaoImpl implements BookDao {
    @Override
    public List<Book> getBookByPageAndSStr(String pageNum, String bNameS, String cNameS) throws SQLException {
        //默认分页长度为10
        int pageLen = 10;
        //查询sql语句
        String sql;
        //当不需要查询字符串时
        String sql1 = "SELECT b.id b_id,b.name b_name,b.author author,b.price price,b.imageName imageName,b.description description,c.id c_id,c.name c_name FROM book b,category c";
        if (bNameS == null && cNameS == null) {
            sql = sql1+" WHERE b.category_id=c.id"+" LIMIT " +
                    (Integer.parseInt(pageNum) - 1) * pageLen + "," + Integer.parseInt(pageNum) * pageLen + " ";
        } else if (bNameS == null) {
            sql = sql1 + " WHERE c.name LIKE '%" + cNameS + "%'" +
                    " AND b.category_id=c.id"+" LIMIT " +
                    (Integer.parseInt(pageNum) - 1) * pageLen + "," + Integer.parseInt(pageNum) * pageLen + " ";
        } else if (cNameS == null) {
            sql = sql1 + " WHERE b.name LIKE '%" + bNameS + "%'" +
                    " AND b.category_id=c.id"+" LIMIT " +
                    (Integer.parseInt(pageNum) - 1) * pageLen + "," + Integer.parseInt(pageNum) * pageLen + " ";
        } else {
            sql = sql1 + " WHERE c.name LIKE '%" + cNameS + "%'" +
                    " AND b.name LIKE '%" + bNameS + "%'" +
                    " AND b.category_id=c.id"+" LIMIT " +
                    (Integer.parseInt(pageNum) - 1) * pageLen + "," + Integer.parseInt(pageNum) * pageLen + " ";;
        }
        //开始查询
        List<Book> books = new ArrayList<Book>();
        ResultSet rs = DbHelper.executeQuery(sql, null);
        while (rs.next()) {
            Book book = new Book();
            book.setId(rs.getString("b_id"));
            book.setName(rs.getString("b_name"));
            book.setAuthor(rs.getString("author"));
            book.setPrice(rs.getFloat("price"));
            book.setImageName(rs.getString("imageName"));
            book.setDescription(rs.getString("description"));
            book.setCategory_id(rs.getString("c_id"));
            book.setGenerate_category_name(rs.getString("c_name"));

            books.add(book);
        }
        //关闭连接
        C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        //返回数据
        return books;
    }

    @Override
    public Book getBookById(String id) throws SQLException {
        String sql="select * from book,category where book.id=? and book.category_id=category.id and book.is_deleted=0";
        ResultSet rs=DbHelper.executeQuery(sql,new Object[]{id});
        Book book=new Book();
        while (rs.next()){
            book.setId(rs.getString("book.id"));
            book.setName(rs.getString("book.name"));
            book.setAuthor(rs.getString("author"));
            book.setPrice(rs.getFloat("price"));
            book.setImageName(rs.getString("imageName"));
            book.setDescription(rs.getString("book.description"));
            book.setCategory_id(rs.getString("category_id"));
            book.setGenerate_category_name(rs.getString("category.name"));

        }
        return book;
    }

    @Override
    public String getBookCount(String[] whereColumn, String[] whereValue) throws SQLException {
        String count=null;
        StringBuilder sql = null;
        //只返回数量
            sql = new StringBuilder("select count(*) from book,category where book.category_id=category.id ");
            if (whereColumn != null) {
                for (String wc : whereColumn) {
                    sql.append(" AND ").append(wc).append(" =?");
                }
            }
            ResultSet rs = DbHelper.executeQuery(sql.toString(), whereValue);
            while (rs.next()) {
                count=rs.getString(1);
            }
            //关闭连接
            C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        return count;
    }

    @Override
    public List<Book> getBook(String[] whereColumn, String[] whereValues) throws SQLException {
        Map<String,Object> result = new HashMap<>();
        StringBuilder sql = null;
        List<Book> books = new ArrayList<>();

        sql = new StringBuilder("select * from book,category where book.category_id = category.id");
        if (whereColumn != null) {
            for (String wc : whereColumn) {
                sql.append(" AND ").append(wc).append(" =?");
            }
        }
        ResultSet rs = DbHelper.executeQuery(sql.toString(),whereValues);
        while (rs.next()){
            Book book = new Book();
            book.setId(rs.getString("id"));
            book.setName(rs.getString("name"));
            book.setAuthor(rs.getString("author"));
            book.setPrice(rs.getFloat("price"));
            book.setImageName(rs.getString("imageName"));
            book.setDescription(rs.getString("description"));
            book.setCategory_id(rs.getString("category_id"));
            book.setGenerate_category_name(rs.getString("category.name"));
            books.add(book);
        }
        C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        return books;
    }

    @Override
    public Map<String, Object> updateBookById(List<Book> books, int selectModel) {
        Map<String,Object> result = new HashMap<>();
        List<Integer> line = new ArrayList<>();
        //update category_id
        if(selectModel==1){
            for(Book book:books){
                line.add(DbHelper.update("book",new String[]{"category_id"},
                        new String[]{book.getCategory_id().toString()},
                        new String[]{"id"},
                        new String[]{book.getId()}));
            }
            result.put("line", line);
        }
        return result;
    }

    @Override
    public Integer insertOneBook(Book book) {
        String sql = "insert into book (id,name,author,price,imageName,description,category_id,is_deleted) values(?,?,?,?,?,?,?,'0')";
        return DbHelper.executeUpdate(sql,new Object[]{
                book.getId(),
                book.getName(),
                book.getAuthor(),
                book.getPrice(),
                book.getImageName(),
                book.getDescription(),
                book.getCategory_id()
        });
    }
}
